package DataAnalysis_ljy

import org.apache.spark.sql.SparkSession
import org.apache.spark.sql.functions.{col, desc}

import java.util.Properties

object t4 {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder()
      .master("local[*]")
      .appName("数据分析第一题")
      .enableHiveSupport()
      .getOrCreate()

    // 准备连接mysql的配置
    val conn = new Properties()
    conn.setProperty("user", "root")
    conn.setProperty("password", "123456")
    conn.setProperty("driver", "com.mysql.jdbc.Driver")

    //  todo 读取已经处理好的数据
    spark.read.jdbc("jdbc:mysql://192.168.40.110:3306/ljy?useSSL=false", "dwd", conn)
      .filter(col("users") !== "众多明星")
      .createOrReplaceTempView("data")

    //  todo 分析单人演唱会价格最贵的几个明星的价格
    val r1 = spark.sql(
      """
        |select distinct
        |users,
        |max(max) over(partition by users) as max_money
        |from data
        |""".stripMargin)

    r1.orderBy(desc("max_money"))
      .show

    r1.write.format("overwrite")
      .jdbc("jdbc:mysql://192.168.40.110:3306/ljy?useSSL=false", "r4", conn)


    spark.close()
  }

}
